﻿using CommonLib.Core;
using Newtonsoft.Json;
using PddHelper.Api;
using PddHelper.Framework;
using PddHelper.Models;
using SqlSugar;
using System.Diagnostics;
using System.Linq;
using System.Linq.Expressions;

namespace WebApiExtend
{
    /// <summary>
    /// 
    /// </summary>
    public class SqlClient<T> where T : class, new() //: SqlClientBase where T : class, new()
    {
        /// <summary>
        /// 当前登录用户ID
        /// </summary>
        public int UserID { get; set; } = 0;

        /// <summary>
        /// 
        /// </summary>
        private ISugarQueryable<T> Query;

        #region SqlSugarClient 超高性能

        /// <summary>
        /// SqlSugarClient操作数据库是线程安的可以单例
        /// </summary>
        /// <param name="name">读/写</param>
        /// <returns></returns>
        public SqlSugarClient dbContext
        {
            get
            {
                //创建数据库对象 (用法和EF Dappper一样通过new保证线程安全)
                //可以在连接字符串中设置连接池pooling=true;表示开启连接池
                //eg:min pool size=2;MaxPoolSize=4;表示最小连接池为2，最大连接池是4；默认是100
                SqlSugarClient DB = new SqlSugarClient(new ConnectionConfig()
                {
                    DbType = SqlSugar.DbType.MySql,
                    ConnectionString = ConfigurationHelper.configuration.GetSection($"DataBase:ConnString")?.Value,
                    //ConnectionString = "server=rm-2ze29it63mpgq7l0iuo.mysql.rds.aliyuncs.com;port=3306;database=pdd;uid=wangshuyu01;pwd=WangShuYu01@654321;CharSet=utf8;Allow User Variables=True;sslMode=None;",
                    IsAutoCloseConnection = true,
                    InitKeyType = InitKeyType.Attribute //从实体特性中读取主键自增列信息
                },
                db =>
                { 
                    db.Aop.OnLogExecuting = (sql, pars) =>
                    {
                        var _sql = sql;
                        var _pars = pars;

                        //Console.WriteLine(UtilMethods.GetNativeSql(sql, pars));//获取原生SQL推荐 5.1.4.63  性能OK                    
                        //Console.WriteLine(UtilMethods.GetSqlString(DbType.SqlServer,sql,pars))//获取无参数化SQL 对性能有影响，特别大的SQL参数多的，调试使用
                    };
                }); 
                return DB;
            }
        }
        #endregion

        #region 构造函数 

        /// <summary>
        /// 读库
        /// </summary> 
        public SqlClient(int userid = 0)
        {
            this.UserID = userid;
            this.Query = dbContext.Queryable<T>();
        }
        /// <summary>
        /// 读库
        /// </summary> 
        public SqlClient(HttpContext httpContext)
        {
            var userid = httpContext.Session.GetString("userid");
            if (userid != null)
            {
                this.UserID = Convert.ToInt32(userid);
            }
            this.Query = dbContext.Queryable<T>();
        }
        #endregion


        #region 聚合函数

        /// <summary>
        /// 根据主键获取实体
        /// </summary>
        /// <param name="id">主键</param>
        /// <returns>返回实体</returns>
        public virtual T GetById(int? id)
        {
            var ret = Query.InSingle(id);
            return ret;
        }

        /// <summary>
        /// 最大值
        /// </summary>
        /// <param name="expression"></param>
        /// <returns>返回实体</returns>
        public virtual TResult Max<TResult>(Expression<Func<T, TResult>> expression)
        {
            var ret = Query.Max(expression);
            return ret;
        }

        /// <summary>
        /// 最小值
        /// </summary>
        /// <param name="expression"></param>
        /// <returns>返回实体</returns>
        public virtual TResult Min<TResult>(Expression<Func<T, TResult>> expression)
        {
            var ret = Query.Min(expression);
            return ret;
        }

        /// <summary>
        /// 求和
        /// </summary>
        /// <param name="expression"></param>
        /// <returns>返回实体</returns>
        public virtual TResult Sum<TResult>(Expression<Func<T, TResult>> expression)
        {
            var ret = Query.Sum(expression);
            return ret;
        }

        /// <summary>
        /// 平均值
        /// </summary>
        /// <param name="expression"></param>
        /// <returns>返回实体</returns>
        public virtual TResult Avg<TResult>(Expression<Func<T, TResult>> expression)
        {
            var ret = Query.Avg(expression);
            return ret;
        }
        #endregion

        #region 条件查询 分页

        /// <summary>
        /// 查询条件 lambda
        /// </summary>
        /// <param name="expression"></param>
        /// <returns></returns>
        public SqlClient<T> Where(Expression<Func<T, bool>> expression)
        {
            Query.Where(expression);
            return this;
        }

        /// <summary>
        /// 查询条件, 字符串类型
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public SqlClient<T> Where(List<FilterModel> filter)
        {
            var _filters = new List<IConditionalModel>();

            if (filter == null)//用户未传isdel,默认增加isdel=1条件
            {
                filter = new List<FilterModel>() { new FilterModel() { Name = "isdel", Type = "=", Value = "0" } };
            }
            else if (!filter.Any(ex => ex.Name.ToLower() == "isdel"))
            {
                filter.Add(new FilterModel() { Name = "isdel", Type = "=", Value = "0" });
            }

            foreach (var item in filter)
            {
                _filters.Add(new ConditionalModel()
                {
                    ConditionalType = GetSqlType(item.Type),
                    FieldName = item.Name,
                    FieldValue = item.Value
                });
            }

            Query.Where(_filters);
            return this;
        }

        /// <summary>
        /// 排序, id desc/id asc
        /// </summary>
        /// <param name="orderby"></param> 
        /// <returns></returns>
        public SqlClient<T> OrderBy(string orderby)
        {
            Query.OrderBy(orderby);
            return this;
        }

        /// <summary>
        /// 排序, id desc/id asc
        /// </summary>
        /// <param name="orderby"></param> 
        /// <returns></returns>
        public SqlClient<T> OrderBy(List<string> orderby)
        {
            foreach (var item in orderby)
            {
                Query.OrderBy(item);
            }
            return this;
        }

        /// <summary>
        /// 排序 正序
        /// </summary>
        /// <param name="expression"></param>
        /// <returns></returns>
        public SqlClient<T> OrderBy(Expression<Func<T, object>> expression)
        {
            Query.OrderBy(expression);
            return this;
        }

        /// <summary>
        /// 排序 倒序
        /// </summary>
        /// <param name="expression"></param>
        /// <returns></returns>
        public SqlClient<T> OrderByDescending(Expression<Func<T, object>> expression)
        {
            Query.OrderByDescending(expression);
            return this;
        }

        /// <summary>
        /// 分页结果, 这一步才真正查询数据库
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public ApiResult<List<T>> ToPageList(int pageIndex = 1, int pageSize = 20)
        {
            int dataCount = 0;
            var list = this.Query.ToPageList(pageIndex, pageSize, ref dataCount);

            var ret = new ApiResult<List<T>>()
            {
                Data = list, 
                total = dataCount,
                pageNumber = pageIndex,
                pageSize = pageSize,
                PageCount = (dataCount % pageSize == 0) ? ((dataCount / pageSize)) : ((dataCount / pageSize) + 1)
            };
            return ret;
        }

        /// <summary>
        /// 配合前端搜索查询
        /// </summary>
        /// <param name="par">查询条件</param>
        /// <returns></returns>
        public ApiResult<List<T>> ToPageList(QueryParameters par)
        {
            this.Where(par.Filters);
            par.OrderBy.Add("id desc");//默认排序 id desc
            this.OrderBy(par.OrderBy);

            var ret = this.ToPageList(par.pageNumber, par.pageSize);
            return ret;
        }

        /// <summary>
        /// 结果,这一步才真正查询数据库
        /// </summary>
        /// <param name="isdel">true-只查询isdel=0的数据, 默认查询全部</param>
        /// <returns></returns>
        public List<T> ToList(bool isdel = false)
        {
            if (isdel)
            {
                var expression = new LambdaExtension<T>().Equal("isdel", 0).expression;
                this.Query.Where(expression);
            }
            var list = this.Query.ToList();
            return list;
        }

        /// <summary>
        /// 结果,这一步才真正查询数据库
        /// </summary>
        /// <param name="isdel">true-只查询isdel=0的数据, 默认查询全部</param>
        /// <returns></returns>
        public List<T> TopToList(int top, bool isdel = false)
        {
            if (isdel)
            {
                var expression = new LambdaExtension<T>().Equal("isdel", 0).expression;
                this.Query.Where(expression);
            }
            var list = this.Query.Take(top).ToList();
            return list;
        }

        /// <summary>
        /// 结果第一条,这一步才真正查询数据库
        /// </summary>
        /// <returns></returns>
        public T? FirstOrDefault()
        {
            var ret = Query.ToList().FirstOrDefault();
            return ret;
        }

        /// <summary>
        /// 获取查询数量
        /// </summary>
        /// <returns></returns>
        public int ToCount(bool isdel = false)
        {
            if (isdel)
            {
                var expression = new LambdaExtension<T>().Equal("isdel", 0).expression;
                this.Query.Where(expression);
            }
            var count = Query.Count();
            return count;
        }

        /// <summary>
        /// 查询某几个字段
        /// </summary>
        /// <param name="expression"></param>
        /// <returns>返回实体</returns>
        public virtual SqlClient<T> Select<TResult>(Expression<Func<T, TResult>> expression)
        {
            this.Query.Select(expression);
            return this;
        }
        #endregion

        #region 数据库字符串类型转枚举

        /// <summary>
        /// 数据库字符串类型转枚举
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        private ConditionalType GetSqlType(string type)
        {
            var ret = ConditionalType.Equal;
            switch (type.ToLower().Trim())
            {
                case "=":
                    {
                        ret = ConditionalType.Equal;
                        break;
                    }
                case "!=":
                    {
                        ret = ConditionalType.NoEqual;
                        break;
                    }
                case ">":
                    {
                        ret = ConditionalType.GreaterThan;
                        break;
                    }
                case ">=":
                    {
                        ret = ConditionalType.GreaterThanOrEqual;
                        break;
                    }
                case "<":
                    {
                        ret = ConditionalType.LessThan;
                        break;
                    }
                case "<=":
                    {
                        ret = ConditionalType.LessThanOrEqual;
                        break;
                    }
                case "in":
                    {
                        ret = ConditionalType.In;
                        break;
                    }
                case "not in":
                    {
                        ret = ConditionalType.NotIn;
                        break;
                    }
                case "like":
                    {
                        ret = ConditionalType.Like;
                        break;
                    }
                case "not like":
                    {
                        ret = ConditionalType.NoLike;
                        break;
                    }
                case "is not":
                    {
                        ret = ConditionalType.IsNot;
                        break;
                    }
            }
            return ret;
        }
        #endregion


        #region 添加

        /// <summary>
        /// 插入数据（适用于id自动增长）
        /// </summary>
        /// <param name="entity">实体对象</param>
        /// <returns>返回主键ID</returns>
        public virtual int Insert(T entity)
        {
            if (entity.GetValue<T>("addtime") == null)
            {
                entity.SetValue("addtime", DateTime.Now);
            }
            if (entity.GetValue<T>("lasttime") == null)
            {
                entity.SetValue("lasttime", DateTime.Now);
            }
            if (entity.GetValue<T>("lastuserid") == null)
            {
                entity.SetValue("lastuserid", this.UserID);
            }
            if (entity.GetValue<T>("adduserid") == null)
            {
                entity.SetValue("adduserid", this.UserID);
            }
            if (entity.GetValue<T>("isdel") == null)
            {
                entity.SetValue("isdel", 0);
            }

            var i = dbContext.Insertable(entity).ExecuteReturnIdentity();
            return i;
        }

        /// <summary>
        /// 插入数据（适用于id自动增长）
        /// </summary>
        /// <param name="entity">实体对象</param>
        /// <returns>返回主键ID</returns>
        public virtual int Insert(List<T> entitys)
        {
            foreach (var entity in entitys)
            {
                entity.SetValue("addtime", DateTime.Now);
                entity.SetValue("lasttime", DateTime.Now);
                entity.SetValue("lastuserid", this.UserID);
                entity.SetValue("adduserid", this.UserID);
                entity.SetValue("isdel", 0);
            }

            var i = dbContext.Insertable(entitys).ExecuteReturnIdentity();
            return i;
        }
        #endregion

        #region 修改

        /// <summary>
        /// 通过主键修改（包含是否需要将null值字段提交到数据库）
        /// </summary>
        /// <param name="newEntity">实体对象</param>
        /// <param name="IgnoreColumns">忽略字段（不更新字段）</param>
        /// <param name="isNoUpdateNull">是否排除NULL值字段更新,默认是</param>
        /// <returns></returns>
        public virtual int Update(T newEntity, List<string>? IgnoreColumns = null, bool isNoUpdateNull = true)
        {
            var id = newEntity.GetValue("id");//获取主键值

            var oldEntity = this.GetById(Convert.ToInt32(id));//获取原来数据库值
            var _ignoreColumns = new List<string>() { "addtime", "lastuserid", "isdel" };
            if (IgnoreColumns != null)
            {
                _ignoreColumns.AddRange(IgnoreColumns);
            }
            newEntity.CopyTo(oldEntity, _ignoreColumns);//赋值 忽略初始3个字段

            //重新赋值修改时间 
            oldEntity.SetValue("lasttime", DateTime.Now);
            oldEntity.SetValue("adduserid", this.UserID);

            //NULL列不更新 ignoreAllNullColumns:true
            var flag = dbContext.Updateable(oldEntity).IgnoreColumns(ignoreAllNullColumns: isNoUpdateNull).ExecuteCommand();
            return flag;
        }
        #endregion

        #region 批量修改

        /// <summary>
        /// 通过主键修改（包含是否需要将null值字段提交到数据库）
        /// </summary>
        /// <param name="list">实体对象</param>
        /// <param name="IgnoreColumns">忽略字段（不更新字段）</param>
        /// <returns></returns>
        public virtual bool UpdateBatch(List<T> list, List<string>? IgnoreColumns = null)
        {
            //NULL列不更新 ignoreAllNullColumns:true
            var flag = dbContext.Updateable(list).ExecuteCommand() > 0;
            return flag;
        }
        #endregion

        #region 删除

        /// <summary>
        /// 逻辑删除
        /// </summary>
        /// <param name="id">主键</param>
        /// <returns>是否删除成功</returns>
        //[Obsolete("不允许真实删除数据,只能逻辑删除数据,请使用Update方法")]
        public virtual bool Delete(int id)
        {
            var entity = this.GetById(id);

            //重新赋值修改时间 
            entity.SetValue("isdel", 1);
            entity.SetValue("lasttime", DateTime.Now);
            entity.SetValue("adduserid", this.UserID);

            var flag = dbContext.Updateable(entity).ExecuteCommand() > 0;
            return flag;
        }
        #endregion 
    }
}
